package com.day240701.dao;

import com.day240701.entity.Student;
import com.day240701.util.JDBCUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    /**
     * 查询全部学生信息
     * @return 返回学生集合
     */
    public List<Student> queryAllStudents() {
        String sql = "select sno, sname, password, sex, age, birthday from t_student";
        ResultSet rs = JDBCUtils.doQuery(sql);

        ArrayList<Student> sList = new ArrayList<>();
        try {
            while (rs.next()) {
                Student stu = new Student();
                stu.setSNo(rs.getInt("sno"));
                stu.setSName(rs.getString("sname"));
                stu.setPassword(rs.getString("password"));
                stu.setSex(rs.getString("sex"));
                stu.setAge(rs.getInt("age"));
                stu.setBirthday(rs.getDate("birthday"));
                sList.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.doClose(rs);
        }
        return sList;
    }

    /**
     * 添加学生信息
     * @param stu 学生对象
     * @return 是否成功
     */
    public boolean addStudent(Student stu) {
        String sql = "insert into t_student(sname, password, sex, age) values(?,?,?,?)";
        int count = JDBCUtils.doUpdate(sql, stu.getSName(), stu.getPassword(), stu.getSex(), stu.getAge());
        return count > 0;
    }

    /**
     * 登录方法
     * @param sNo 学号
     * @param password 密码
     * @return 登录是否成功
     */
    public boolean login(int sNo, String password) {
        String sql = "select sno from t_student where sno=? and password=?";
        ResultSet rs = JDBCUtils.doQuery(sql, sNo, password);
        try {
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 模糊查询 根据姓名查询学生信息
     * @param sName 名字
     * @return 学生集合
     */
    public List<Student> queryStudentBySName(String sName) {
        String sql = "select sno, sname, password, sex, age, birthday from t_student where sname like concat('%', ?, '%')";
        ResultSet rs = JDBCUtils.doQuery(sql, sName);

        ArrayList<Student> sList = new ArrayList<>();
        try {
            while (rs.next()) {
                Student stu = new Student();
                stu.setSNo(rs.getInt("sno"));
                stu.setSName(rs.getString("sname"));
                stu.setPassword(rs.getString("password"));
                stu.setSex(rs.getString("sex"));
                stu.setAge(rs.getInt("age"));
                stu.setBirthday(rs.getDate("birthday"));
                sList.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.doClose(rs);
        }
        return sList;
    }

    /**
     * 修改学生信息
     * @param student 新的学生对象
     * @return 是否修改成功
     */
    public boolean modifyStudent(Student student) {
        String sql = "update t_student set sName=?, password=?, sex=?, age=?, birthday=? where sno=?";
        int count = JDBCUtils.doUpdate(sql, student.getSName(), student.getPassword(), student.getSex(), student.getAge(), student.getBirthday(), student.getSNo());
        return count > 0;
    }

    /**
     * 删除学生信息
     * @param sNo 要删除的学生的学号
     * @return 是否删除成功
     */
    public boolean deleteStudentBySNo(int sNo) {
        String sql = "delete from t_student where sno=?";
        int count = JDBCUtils.doUpdate(sql, sNo);
        return count > 0;
    }

    /*public List<List<Student>> pagedQueryStudents(int ) {

    }*/

    public Student queryMyInfo(int sNo) {
        ResultSet rs = JDBCUtils.doQuery("select sno, sname, password, sex, age from t_student where sno=?", sNo);
        Student my = null;
        try {
            while (rs.next()) {
                my = new Student();
                my.setSNo(sNo);
                my.setSName(rs.getString("sname"));
                my.setPassword(rs.getString("password"));
                my.setSex(rs.getString("sex"));
                my.setAge(rs.getInt("age"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.doClose(rs);
        }
        return my;
    }

    /**
     * 分页的方式查询学生
     * @param pageIndex 第几页
     * @param pageSize 一页几条
     * @return
     */
    public List<Student> queryAllStudentsByPage(int pageIndex, int pageSize) {
        List<Student> sList = new ArrayList<>();
        String sql = "select sno, sname, password, sex, age from t_student limit ?, ?";
        ResultSet rs = JDBCUtils.doQuery(sql, (pageIndex - 1) * pageSize, pageSize);

        try {
            while (rs.next()) {
                Student student = new Student();
                student.setSNo(rs.getInt("sno"));
                student.setSName(rs.getString("sname"));
                student.setPassword(rs.getString("password"));
                student.setSex(rs.getString("sex"));
                student.setAge(rs.getInt("age"));
                sList.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.doClose(rs);
        }
        return sList;
    }

    /**
     * 获得查询全部学生的总条数
     * @return 总条数
     */
    public int queryAllStudentsByPage_count() {
        int totalDataNum = 0;
        String sql = "select count(*) count from t_student";

        ResultSet rs = JDBCUtils.doQuery(sql);

        try{
            while (rs.next()) {
                totalDataNum = rs.getInt("count");
            }
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.doClose(rs);
        }
        return totalDataNum;
    }

    /**
     * 分页的方式查询2（加入了查询条件）
     * @param sNo
     * @param sName
     * @param sex
     * @param age
     * @param pageIndex
     * @param pageSize
     * @return
     */
    public List<Student> queryAllStudentsByPage2(String sNo, String sName, String sex, String age, int pageIndex, int pageSize) {
        List<Student> sList = new ArrayList<>();
        // todo: 把 null 的数据替换成空字符串 参考老师的 commit: `ajax的开篇和运用4（ajax的同步和异步，在分页中的运用1）`
        String sql = "select sno, sname, password, sex, age from t_student where sNo like ? and sName like ? and sex like ? and age like ? limit ?, ?";
        ResultSet rs = JDBCUtils.doQuery(sql, sNo, sName, sex, age, (pageIndex - 1) * pageSize, pageSize);

        try {
            while (rs.next()) {
                Student student = new Student();
                student.setSNo(rs.getInt("sno"));
                student.setSName(rs.getString("sname"));
                student.setPassword(rs.getString("password"));
                student.setSex(rs.getString("sex"));
                student.setAge(rs.getInt("age"));
                sList.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.doClose(rs);
        }
        return sList;
    }

    /**
     * 获得 查询全部学生的 总条数  带条件
     * @param sNo
     * @param sName
     * @param sex
     * @param age
     * @return
     */
    public int queryAllStudentsByPage2_count(String sNo,String sName,String sex,String age) {
        int totalDataNum = 0;
        String sql = "select count(*) count from t_student where sNo like ? and sName like ? and sex like ? and age like ?";

        ResultSet rs = JDBCUtils.doQuery(sql,sNo,sName,sex,age);

        try{
            while (rs.next()) {
                totalDataNum = rs.getInt("count");
            }
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.doClose(rs);
        }
        return totalDataNum;
    }

    /**
     * 校验 学号 是否存在
     * @param sNo
     * @return
     */
    public boolean isSNoExist(int sNo) {
        boolean flag = false;
        ResultSet rs = JDBCUtils.doQuery("select sno from t_student where sno =?", sNo);

        try {
            if (rs.next()){
                flag = true;
            }
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            JDBCUtils.doClose(rs);
        }
        return flag;
    }

    public static void main(String[] args) {

        /*addStudent(new Student(0, "fwh", "456", "男", 60, null));*/


        StudentDao studentDao = new StudentDao();

        System.out.println(studentDao.queryAllStudentsByPage2("%%", "%h%", "%男%", "%%", 1, 5));
        System.out.println(studentDao.queryAllStudentsByPage2_count("%%", "%h%", "%男%", "%%"));
    }
}
